import pymysql
import xlwt,xlutils
mysql_info = {

    "host": "118.24.3.40",
    "user": "jxz",
    "password": "123456",
    "port": 3306,
    # "db":"jxz",
    "charset": "utf8",
    "autocommit": True
}#连接数据库
def export_to_excel(table_name,db_name):
    mysql_info['db'] = db_name
    str_sql = "select * from %s" % table_name
    conn = pymysql.connect(**mysql_info)
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute(str_sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result
db = export_to_excel('students','jxz')
db_excel = []
for i in db:  #遍历数据库返回的数据列表
    db_excel.append(list(i.values()))
title = [i for i in db[0]]
db_excel.insert(0,title)
book =xlwt.Workbook()
sheet = book.add_sheet("stu")
for row,i in enumerate(db_excel):
    for col ,j in enumerate(i):
        sheet.write(row,col,j)
book.save("stu.xls")

